﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void MapICC_VKPRICE(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveICC_VKPRICE(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            Result = QueryICC_VKPRICE(dwData);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }

            MapICC_VKPRICE(ref dwLocal, dwData);

            if (!flgExist)
            {
                InsertICC_VKPRICE(dwData, 1);
            }
            UpdateICC_VKPRICE(dwLocal);
        }
        public void InsertICC_VKPRICE(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    break;
                case 2:
                    or1.CommandText = "INSERT INTO ICC_VKPRICE (CONTROL,ROWNID,MANDANT,MKUNDER,KDKUNDNA,VKPAUFKRS,VKPAUFLFD,VKPAUFPOS, VKPKOLL,VKPART,"
                        + "VAABEZ,VKPQTY,VKPUNIT,USD2RMB,CURRENCY,MCPRICE,PREH,NEWUSER)  SELECT 'NO' AS CONTROL,SEQ_ICC_ITX.NEXTVAL AS ROWNID,X.* FROM"
                        + " (SELECT VKKMANDANT AS MANDANT,MAX(VKKKUNDNR) AS MKUNDER, MAX(KDKUNDNA) AS KDKUNDNA,"
                        + " VKKAUFKRS AS VKPAUFKRS,VKKAUFLFD AS VKPAUFLFD,COUNT(*) AS VKPAUFPOS,MAX(VKPKOLL) AS VKPKOLL,"
                        + "  MAX(VKPART) AS VKPART,MAX(SUBSTR(SDDATEN,2,22)) AS VAABEZ, SUM(VKPMENGE1*0.01) AS VKPQTY,MAX(VKPMEEH) AS VKPUNIT,"
                        + "  MAX(TO_NUMBER(VKKKURS))*0.0001 AS USD2RMB, MAX(VKKWAEHRSL) AS CURRENCY,MAX(VKPPREIS*0.001) AS MCPRICE,MAX(VKPPREH) AS PREH,MAX(VKKNEUBEN) AS NEWUSER "
                        + "  FROM VKKOPF K  JOIN KDSTAMM ON VKKKUNDNR=KDKUNDNR  JOIN VKPOS ON VKPSA='  ' AND VKKSA='  ' AND VKPMANDANT=VKKMANDANT"
                        + " AND VKKAUFKRS=VKPAUFKRS AND VKKAUFLFD= VKPAUFLFD JOIN VAART ON VKPKOLL=VAAKOLL AND VKPART=VAAART LEFT JOIN SDDAT ON SDSA='0178' AND SDKEY = '00'||VAAARTGRP||'                  V'"
                        + " GROUP BY VKKMANDANT,VKKAUFKRS,VKKAUFLFD) X "
                        + " WHERE  NOT EXISTS (SELECT 1 FROM ICC_VKPRICE P WHERE P.VKPAUFKRS=X.VKPAUFKRS AND P.VKPAUFLFD=X.VKPAUFLFD)";
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet QueryICC_VKPRICE(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText =" SELECT I.*,VKKLIEFBED FROM ICC_VKPRICE I LEFT JOIN VKKOPF ON VKKAUFKRS=:VKPAUFKRS AND VKKAUFLFD=:VKPAUFLFD WHERE  ROWNID=:ROWNID"; ;
                    or1.Parameters.Add("ROWNID", OracleType.NVarChar).Value = dwData["ROWNID"].ToString();
                    or1.Parameters.Add("VKPAUFKRS", OracleType.NVarChar).Value = dwData["VKPAUFKRS"].ToString();
                    or1.Parameters.Add("VKPAUFLFD", OracleType.NVarChar).Value = dwData["VKPAUFLFD"].ToString();
                    break;
                case 2:
                    or1.CommandText = "SELECT * FROM ICC_VKPRICE WHERE MANDANT=:MANDANT AND CONTROL=:CONTROL"; ;
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    or1.Parameters.Add("CONTROL", OracleType.NVarChar).Value = dwData["CONTROL"].ToString();
                    break;
                case 3:
                    or1.CommandText = "SELECT * FROM ICC_VKPRICE WHERE MANDANT=:MANDANT AND VKPART LIKE '" + dwData["VKPART"].ToString().Trim() + "%' "//AND NEWTIME LIKE '%" + dwData["NEWTIME"].ToString().Trim() + "%'"
                         + " ORDER BY NEWTIME";
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    break;
                case 4:
                    or1.CommandText = "SELECT * FROM ICC_VKPRICE WHERE MANDANT=:MANDANT AND VKPAUFKRS=:VKPAUFKRS AND VKPAUFLFD=:VKPAUFLFD"
                         + " ORDER BY VKPAUFKRS,VKPAUFLFD,MTYE,MPOS";
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    or1.Parameters.Add("VKPAUFKRS", OracleType.NVarChar).Value = dwData["VKPAUFKRS"].ToString();
                    or1.Parameters.Add("VKPAUFLFD", OracleType.NVarChar).Value = dwData["VKPAUFLFD"].ToString();
                    break;
                case 5:
                    or1.CommandText = "SELECT P.* from ICC_VKPRICE P WHERE   not  EXISTS (SELECT 1 FROM vkpos x WHERE X.VKPSA='  ' AND P.VKPAUFKRS=X.VKPAUFKRS AND P.VKPAUFLFD=X.VKPAUFLFD and p.vkpart=x.vkpart)";
                    break;

                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "ICC_VKPRICE";
            Return = Result;
            return Return;
        }
        public void DeleteICC_VKPRICE(DataRow dwData, int option = 1)
        {
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM ICC_VKPRICE WHERE  ROWNID=:ROWNID"; ;
                    or1.Parameters.Add("ROWNID", OracleType.NVarChar).Value = dwData["ROWNID"].ToString();
                    DeleteICC_VKPWARP(dwData, 2);
                    DeleteICC_VKPWEFT(dwData, 2);
                    break;
            }
            Connect2Proxy(or1);
        }
        public void UpdateICC_VKPRICE(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            #region====Command====
            or1.CommandText = "UPDATE ICC_VKPRICE SET "
            + "CONTROL=:CONTROL,"
            + "MANDANT=:MANDANT,"
            + "ROWNID=:ROWNID,"
            + "MKUNDER=:MKUNDER,"
            + "KDKUNDNA=:KDKUNDNA,"
            + "VKPAUFKRS=:VKPAUFKRS,"
            + "VKPAUFLFD=:VKPAUFLFD,"
            + "VKPAUFPOS=:VKPAUFPOS,"
            + "VKPKOLL=:VKPKOLL,"
            + "VKPART=:VKPART,"
            + "VAABEZ=:VAABEZ,"
            + "CONSTRUCTION=:CONSTRUCTION,"
            + "VKPQTY=:VKPQTY,"
            + "VKPUNIT=:VKPUNIT,"
            + "MVKWEFTDEN=:MVKWEFTDEN,"
            + "USD2RMB=:USD2RMB,"
            + "CURRENCY=:CURRENCY,"
            + "MCPRICE=:MCPRICE,"
            + "PREH=:PREH,"
            + "MVKCOST=:MVKCOST,"
            + "MCOSTMW=:MCOSTMW,"
            + "MCOSTMF=:MCOSTMF,"
            + "MCOSTMY=:MCOSTMY,"
            + "MCOSTDW=:MCOSTDW,"
            + "MCOSTDF=:MCOSTDF,"
            + "MCOSTDY=:MCOSTDY,"
            + "MCOSTFIN=:MCOSTFIN,"
            + "MCOSTPAK=:MCOSTPAK,"
            + "MCOSTFVV=:MCOSTFVV,"
            + "MCOSTFADD=:MCOSTFADD,"
            + "MCOSTPROD=:MCOSTPROD,"
            + "MCOSTU=:MCOSTU,"
            + "MPRICEAGENT=:MPRICEAGENT,"
            + "MCOSTAGENT=:MCOSTAGENT,"
            + "MUNITAGENT=:MUNITAGENT,"
            + "MPRICESHIP=:MPRICESHIP,"
            + "MCOSTSHIP=:MCOSTSHIP,"
            + "MUNITSHIP=:MUNITSHIP,"
            + "MPRICEFLIGHT=:MPRICEFLIGHT,"
            + "MCOSTFLIGHT=:MCOSTFLIGHT,"
            + "MUNITFLIGHT=:MUNITFLIGHT,"
            + "MCOSTADD=:MCOSTADD,"
            + "MUNITADD=:MUNITADD,"
            + "MCOSTGROSS=:MCOSTGROSS,"
            + "MCOSTNET=:MCOSTNET,"
            + "MCOSTNETW=:MCOSTNETW,"
            + "MCOSTF=:MCOSTF,"
            + "REMARK=:REMARK,"
            + "NEWUSER=:NEWUSER,"
            + "NEWTIME=:NEWTIME,"
            + "CHGUSER=:CHGUSER,"
            + "CHGTIME=:CHGTIME "
                + " WHERE  "
                + "    ROWNID=:ROWNID ";
            #endregion
            #region====Parameters====
            or1.Parameters.Add("CONTROL", OracleType.NVarChar).Value = dwData["CONTROL"].ToString();
            or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
            or1.Parameters.Add("ROWNID", OracleType.NVarChar).Value = dwData["ROWNID"].ToString();
            or1.Parameters.Add("MKUNDER", OracleType.NVarChar).Value = dwData["MKUNDER"].ToString();
            or1.Parameters.Add("KDKUNDNA", OracleType.NVarChar).Value = dwData["KDKUNDNA"].ToString();
            or1.Parameters.Add("VKPAUFKRS", OracleType.NVarChar).Value = dwData["VKPAUFKRS"].ToString();
            or1.Parameters.Add("VKPAUFLFD", OracleType.NVarChar).Value = dwData["VKPAUFLFD"].ToString();
            or1.Parameters.Add("VKPAUFPOS", OracleType.NVarChar).Value = dwData["VKPAUFPOS"].ToString();
            or1.Parameters.Add("VKPKOLL", OracleType.NVarChar).Value = dwData["VKPKOLL"].ToString();
            or1.Parameters.Add("VKPART", OracleType.NVarChar).Value = dwData["VKPART"].ToString();
            or1.Parameters.Add("VAABEZ", OracleType.NVarChar).Value = dwData["VAABEZ"].ToString();
            or1.Parameters.Add("CONSTRUCTION", OracleType.NVarChar).Value = dwData["CONSTRUCTION"].ToString();
            or1.Parameters.Add("VKPQTY", OracleType.NVarChar).Value = dwData["VKPQTY"].ToString();
            or1.Parameters.Add("VKPUNIT", OracleType.NVarChar).Value = dwData["VKPUNIT"].ToString();
            or1.Parameters.Add("MVKWEFTDEN", OracleType.NVarChar).Value = dwData["MVKWEFTDEN"].ToString();
            or1.Parameters.Add("USD2RMB", OracleType.NVarChar).Value = dwData["USD2RMB"].ToString();
            or1.Parameters.Add("CURRENCY", OracleType.NVarChar).Value = dwData["CURRENCY"].ToString();
            or1.Parameters.Add("MCPRICE", OracleType.NVarChar).Value = dwData["MCPRICE"].ToString();
            or1.Parameters.Add("PREH", OracleType.NVarChar).Value = dwData["PREH"].ToString();
            or1.Parameters.Add("MVKCOST", OracleType.NVarChar).Value = dwData["MVKCOST"].ToString();
            or1.Parameters.Add("MCOSTMW", OracleType.NVarChar).Value = dwData["MCOSTMW"].ToString();
            or1.Parameters.Add("MCOSTMF", OracleType.NVarChar).Value = dwData["MCOSTMF"].ToString();
            or1.Parameters.Add("MCOSTMY", OracleType.NVarChar).Value = dwData["MCOSTMY"].ToString();
            or1.Parameters.Add("MCOSTDW", OracleType.NVarChar).Value = dwData["MCOSTDW"].ToString();
            or1.Parameters.Add("MCOSTDF", OracleType.NVarChar).Value = dwData["MCOSTDF"].ToString();
            or1.Parameters.Add("MCOSTDY", OracleType.NVarChar).Value = dwData["MCOSTDY"].ToString();
            or1.Parameters.Add("MCOSTFIN", OracleType.NVarChar).Value = dwData["MCOSTFIN"].ToString();
            or1.Parameters.Add("MCOSTPAK", OracleType.NVarChar).Value = dwData["MCOSTPAK"].ToString();
            or1.Parameters.Add("MCOSTFVV", OracleType.NVarChar).Value = dwData["MCOSTFVV"].ToString();
            or1.Parameters.Add("MCOSTFADD", OracleType.NVarChar).Value = dwData["MCOSTFADD"].ToString();
            or1.Parameters.Add("MCOSTPROD", OracleType.NVarChar).Value = dwData["MCOSTPROD"].ToString();
            or1.Parameters.Add("MCOSTU", OracleType.NVarChar).Value = dwData["MCOSTU"].ToString();
            or1.Parameters.Add("MPRICEAGENT", OracleType.NVarChar).Value = dwData["MPRICEAGENT"].ToString();
            or1.Parameters.Add("MCOSTAGENT", OracleType.NVarChar).Value = dwData["MCOSTAGENT"].ToString();
            or1.Parameters.Add("MUNITAGENT", OracleType.NVarChar).Value = dwData["MUNITAGENT"].ToString();
            or1.Parameters.Add("MPRICESHIP", OracleType.NVarChar).Value = dwData["MPRICESHIP"].ToString();
            or1.Parameters.Add("MCOSTSHIP", OracleType.NVarChar).Value = dwData["MCOSTSHIP"].ToString();
            or1.Parameters.Add("MUNITSHIP", OracleType.NVarChar).Value = dwData["MUNITSHIP"].ToString();
            or1.Parameters.Add("MPRICEFLIGHT", OracleType.NVarChar).Value = dwData["MPRICEFLIGHT"].ToString();
            or1.Parameters.Add("MCOSTFLIGHT", OracleType.NVarChar).Value = dwData["MCOSTFLIGHT"].ToString();
            or1.Parameters.Add("MUNITFLIGHT", OracleType.NVarChar).Value = dwData["MUNITFLIGHT"].ToString();
            or1.Parameters.Add("MCOSTADD", OracleType.NVarChar).Value = dwData["MCOSTADD"].ToString();
            or1.Parameters.Add("MUNITADD", OracleType.NVarChar).Value = dwData["MUNITADD"].ToString();
            or1.Parameters.Add("MCOSTGROSS", OracleType.NVarChar).Value = dwData["MCOSTGROSS"].ToString();
            or1.Parameters.Add("MCOSTNET", OracleType.NVarChar).Value = dwData["MCOSTNET"].ToString();
            or1.Parameters.Add("MCOSTNETW", OracleType.NVarChar).Value = dwData["MCOSTNETW"].ToString();
            or1.Parameters.Add("MCOSTF", OracleType.NVarChar).Value = dwData["MCOSTF"].ToString();
            or1.Parameters.Add("REMARK", OracleType.NVarChar).Value = dwData["REMARK"].ToString();
            or1.Parameters.Add("NEWUSER", OracleType.NVarChar).Value = dwData["NEWUSER"].ToString();
            or1.Parameters.Add("NEWTIME", OracleType.NVarChar).Value = dwData["NEWTIME"].ToString();
            or1.Parameters.Add("CHGUSER", OracleType.NVarChar).Value = dwData["CHGUSER"].ToString();
            or1.Parameters.Add("CHGTIME", OracleType.NVarChar).Value = dwData["CHGTIME"].ToString();
            #endregion
            Connect2Proxy(or1);
        }
    }
}
